package com.eascs.admin.o2o.loan.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.loan.entity.O2oLoanUserEntity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;

/**
 * 
 * @author chenxingwang
 *
 */
@Repository
public class O2oLoanUserDao extends BaseO2oDao {
	/**
	 * 主表loanapplyNo和loanId 查询  主贷款O2oLoanUserEntity实体
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public O2oLoanUserEntity queryLoanpplyNo(String loanapplyNo,String loanId){
		String sql="select * from o2o_loan_user u where u.loanapply_no=? and u.loan_id=?";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo,loanId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               主表loanapplyNo和loanId 查询  主贷款O2oLoanUserEntity实体 脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午5:40:30
	 */
	public O2oLoanUserEntity queryLoanpplyNo_sensitive(String loanapplyNo,String loanId){
		String sql="select CONCAT(LEFT(u.document_no, 3),'****',RIGHT(u.document_no, 3)) AS document_no,CONCAT(LEFT(u.user_phone, 3),'****',RIGHT(u.user_phone, 3)) AS user_phone from o2o_loan_user u where u.loanapply_no=? and u.loan_id=?";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo,loanId);
	}
	
	/**
	 * 主表loanapplyNo借款人、配偶、及担保人信息
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public List<O2oLoanUserEntity> queryUsersByLoanapplyNo(String loanapplyNo){
		String sql="select * from o2o_loan_user u where u.loanapply_no=? ";
		return this.findEntitys(sql, O2oLoanUserEntity.class, loanapplyNo);
	}
	
	/**
	 * 主表loanapplyNo和loanId 查询  主贷款O2oLoanUserEntity实体
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public O2oLoanUserEntity querySponsorById(String loanapplyNo,String loanId){
		String sql="select * from o2o_loan_user u where u.loanapply_no=? and u.loan_id=? and sponsor_type= 'D' ";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo,loanId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               主表loanapplyNo和loanId 查询  主贷款O2oLoanUserEntity实体 脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午4:58:33
	 */
	public O2oLoanUserEntity querySponsorById_sensitive(String loanapplyNo,String loanId){
		String sql="select CONCAT(LEFT(u.user_phone, 3),'****',RIGHT(u.user_phone, 3)) AS user_phone, CONCAT(LEFT(u.document_no, 3),'****',RIGHT(u.document_no, 3)) AS document_no from o2o_loan_user u where u.loanapply_no=? and u.loan_id=? and sponsor_type= 'D' ";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo,loanId);
	}
	
	/**
	 * 主表loanapplyNo 查询  主贷款担保人O2oLoanUserEntity实体
	 * @param loanapplyNo
	 * @return
	 */
	public O2oLoanUserEntity queryDBById(String loanapplyNo){
		String sql = "SELECT * FROM o2o_loan_user u WHERE u.loanapply_no = ? AND u.sponsor_type = 'D'";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo);
	}
	
	/**
	 * 主表loanapplyNo和loanId 查询  配偶 O2oLoanUserEntity实体
	 * @param loanId
	 * @param loanapplyNo
	 * @return
	 */
	public O2oLoanUserEntity findSpouseId(String loanId,String loanapplyNo) {
		String sql="select * from o2o_loan_user u where  u.spouse_id=? and u.loanapply_no=? ";;
		return this.findEntity(sql, O2oLoanUserEntity.class, loanId,loanapplyNo);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               主表loanapplyNo和loanId 查询  配偶 O2oLoanUserEntity实体 脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午5:11:42
	 */
	public O2oLoanUserEntity findSpouseId_sensitive(String loanId,String loanapplyNo) {
		String sql="select CONCAT(LEFT(u.user_phone, 3),'****',RIGHT(u.user_phone, 3)) AS user_phone, CONCAT(LEFT(u.document_no, 3),'****',RIGHT(u.document_no, 3)) AS document_no from o2o_loan_user u where  u.spouse_id=? and u.loanapply_no=? ";;
		return this.findEntity(sql, O2oLoanUserEntity.class, loanId,loanapplyNo);
	}
	
	/**
	 * 校验当前贷款身份证和姓名 ，与上笔贷款身份证和姓名是否匹配
	 * @param loanId
	 * @param userFullName
	 * @param documentNo
	 * @return
	 */
	public int isNotExist(String loanId, String userFullName, String documentNo) {
		String sql="select count(1) from o2o_loan_user t where t.loan_id=? and t.user_full_name=? and t.document_no=?";
		return this.findInteger(sql, loanId,userFullName,documentNo);
	}
	/**
	 * 根椐贷款单号查询所有用户
	 * @param loanapplyNo
	 * @return
	 */
	public List<O2oLoanUserEntity> findSponsor(String loanapplyNo) {
		String sql="select * from o2o_loan_user u where  u.spouse_id is not null and u.loanapply_no=? ";;
		return this.findEntitys(sql, O2oLoanUserEntity.class, loanapplyNo);
	}
	/**
	 * 根椐贷款单号配偶
	 * @param loanapplyNo
	 * @return
	 */	
	public List<O2oLoanUserEntity> findloanORspouse(String loanapplyNo) {
		String sql="select * from o2o_loan_user u where  u.spouse_id is not null and u.loanapply_no=? AND u.sponsor_type is null";;
		return this.findEntitys(sql, O2oLoanUserEntity.class, loanapplyNo);
	}
	/**
	 * @Title: findMateUserEntity
	 * @Description: 根据loanapplyNo,查询配偶id
	 * @author 张庆奇
	 * @param loanapplyNo
	 * @return
	 * @return String
	 * @throws
	 * @date 2017年8月2日 下午3:54:00
	 */
	public String findMateUserEntity(String loanapplyNo){
		String sql="select u.id  from o2o_loan_user u where u.spouse_id is not null and u.sponsor_type is null and loanapply_no=?" ;
		return this.findString(sql, loanapplyNo);
	}
	
	public O2oLoanUserEntity queryLoanUserInfo(String loanId) {
		String sql = "SELECT * FROM o2o_loan_user WHERE loan_id = ?";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanId);
	}
	
	public List<O2oLoanUserEntity> queryDBsById(String loanapplyNo){
		String sql = "SELECT * FROM o2o_loan_user u WHERE u.loanapply_no = ? AND u.sponsor_type = 'D'  ";
		return this.findEntitys(sql, O2oLoanUserEntity.class, loanapplyNo);
	}

	/**
	 * @Title: findByLoanapplyNo
	 * @Description: 根据单款单号查询主贷款人信息
	 * @author 张庆奇
	 * @param loanapplyNo
	 * @return
	 * @return O2oLoanUserEntity
	 * @throws
	 * @date 2017年10月12日 上午9:41:49
	 */
	public O2oLoanUserEntity findByLoanapplyNo(String loanapplyNo) {
		String sql="select * from o2o_loan_user u where  u.spouse_id is  null and u.loanapply_no=? AND u.sponsor_type is null";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo);
	}
	public PageResult<Object[]> findByUserName(String username) {
		StringBuffer sql =new StringBuffer();
		sql.append(" SELECT * FROM (");
		sql.append(" SELECT  r.id,y.bill_code,r.user_full_name,r.document_no,r.user_phone,y.operator_name ,");
		sql.append(" (SELECT l.province_name from o2o_platform_regional l where l.platform_id=y.ltd) as province_name ");
		sql.append(" from o2o_loan_user r,o2o_loanapply y ");
		sql.append(" where r.loanapply_no=y.loanapply_no  and y.bill_code is not NULL and y.loan_flg='N' ");
		sql.append(" union ");
		sql.append(" SELECT u.id,x.bill_code,u.user_full_name,u.document_no,u.user_phone,x.operator_name,");
		sql.append(" (SELECT l.province_name from o2o_platform_regional l where l.platform_id=x.ltd) as province_name");
		sql.append(" from o2o_loan_user_zx u,o2o_zx x ");
		sql.append(" where u.zx_no=x.id and x.id not in (SELECT zx_no from o2o_loan_user  where zx_no is not NULL )");
		sql.append(" ) t where user_full_name  = ? ");
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(username);
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
}
